﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using MergeInfo.Modal.Entity;
using MergeInfo.Modal.Interface.IDAO;

namespace MergeInfo.Modal.DAO
{
    public class DataDictionaryDao : BaseDao, IDataDictionaryDao
    {
        private const string InsertSql = @"INSERT INTO [MergeInfo].[dbo].[DataDictionary]
           ([Id]
           ,[RId]
           ,[Name]
           ,[Value])
     VALUES
           (@Id
           ,@RId
           ,@Name
           ,@Value)";

        private const string SelectSql = @"SELECT [Id]
      ,[Name]
      ,[Value]
      ,[RId]
      ,[RName]
      ,[RValue]
  FROM [MergeInfo].[dbo].[vDataDictionary]";

        public void Create(DataDictionaryEntity dataDictionaryEntity)
        {
            SqlCommand = new SqlCommand { Connection = SqlConnection, CommandText = InsertSql };
            SqlCommand.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int));
            SqlCommand.Parameters["@Id"].Value = dataDictionaryEntity.Id;

            SqlCommand.Parameters.Add(new SqlParameter("@RId", SqlDbType.Int));
            if (dataDictionaryEntity.RId != null)
                SqlCommand.Parameters["@RId"].Value = dataDictionaryEntity.RId.Id;
            else
                SqlCommand.Parameters["@RId"].Value = null;

            SqlCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 50));
            SqlCommand.Parameters["@Name"].Value = dataDictionaryEntity.Name;

            SqlCommand.Parameters.Add(new SqlParameter("@Value", SqlDbType.Int));
            SqlCommand.Parameters["@Value"].Value = dataDictionaryEntity.Value;

            Submit();
        }


        public IList<DataDictionaryEntity> Select(string name)
        {
            const string sql = SelectSql + " where [Name]=@Name";
            SqlCommand = new SqlCommand { Connection = SqlConnection, CommandText = sql };
            SqlCommand.Parameters.Add(new SqlParameter("@Name", name));
            SqlCommand.Connection.Open();
            SqlDataReader sqlDataReader = SqlCommand.ExecuteReader();
            IList<DataDictionaryEntity> dataDictionaryEntities = AssemblyList(sqlDataReader);
            SqlCommand.Connection.Close();
            return dataDictionaryEntities;
        }

        public IList<DataDictionaryEntity> Select(int rid)
        {
            const string sql = SelectSql + " where [RId]=@RId";
            SqlCommand = new SqlCommand { Connection = SqlConnection, CommandText = sql };
            SqlCommand.Parameters.Add(new SqlParameter("@RId", rid));
            SqlCommand.Connection.Open();
            SqlDataReader sqlDataReader = SqlCommand.ExecuteReader();
            IList<DataDictionaryEntity> dataDictionaryEntities = AssemblyList(sqlDataReader);
            SqlCommand.Connection.Close();
            return dataDictionaryEntities;
        }

        private static IList<DataDictionaryEntity> AssemblyList(SqlDataReader sqlDataReader)
        {
            IList<DataDictionaryEntity> dataDictionaryEntities = new List<DataDictionaryEntity>();
            while (sqlDataReader.Read())
            {
                var dataDictionaryEntity = new DataDictionaryEntity
                {
                    Id = Convert.ToInt32(sqlDataReader["Id"].ToString()),
                    Name = sqlDataReader["Name"].ToString(),
                    Value = Convert.ToInt32(sqlDataReader["Value"].ToString())
                };
                if (!string.IsNullOrEmpty(sqlDataReader["RId"].ToString()))
                {
                    dataDictionaryEntity.RId = new DataDictionaryEntity
                    {
                        Id = Convert.ToInt32(sqlDataReader["RId"].ToString()),
                        Name = sqlDataReader["RName"].ToString(),
                        Value = Convert.ToInt32(sqlDataReader["RValue"].ToString())
                    };
                }
                dataDictionaryEntities.Add(dataDictionaryEntity);
            }
            return dataDictionaryEntities;
        }
    }
}
